/*
 * %W% %E%
 * Copyright (c)
 */
package com.shareinfo.adminManage.auto.dao.jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Repository;

import com.shareinfo.adminManage.auto.dao.TableDao;
import com.shareinfo.adminManage.auto.model.AutoDbModel;
import com.shareinfo.adminManage.auto.model.AutoDbTableModel;
import com.shareinfo.auto.vo.ColumnInfo;
import com.shareinfo.auto.vo.ResultInfo;
import com.shareinfo.base.dao.jdbc.SpringJdbcDaoImpl;

/**
 * @ClassName: TableDaoImpl
 * @Description: TODO
 * @author Huangpeng@shareinfo.com.cn
 * @date 2013年9月9日 下午4:52:57
 */
@Repository("tableDao")
public class TableDaoImpl extends SpringJdbcDaoImpl implements TableDao {

	// 判断是否是oracle数据库
	private boolean ifOracleDb = false;
	/**
	 * 在SQL环境下Catalog和Schema都属于抽象概念，可以把它们理解为一个容器或者数据库对象命名空间中的一个层次，
	 * 主要用来解决命名冲突问题。从概念上说，一个数据库系统包含多个Catalog，每个Catalog又包含多个Schema，而
	 * 每个Schema又包含多个数据库对象（表、视图、字段等），反过来讲一个数据库对象必然属于一个Schema，而该Schema
	 * 又必然属于一个Catalog
	 */
	private String catalog = null;
	/**
	 * 如果是oracle的话，则需要指定是那个用户
	 */
	private String schema = null;
	// 数据库连接
	private AutoDbModel autoDbModel;
	// 要返回的结果集合
	private ResultInfo resultInfo;

	/**
	 * 设置数据库信息
	 */
	public void setAutoDbModel(AutoDbModel autoDbModel) {
		this.autoDbModel = autoDbModel;
		if ("oracle".equalsIgnoreCase(autoDbModel.getDbType())) {
			ifOracleDb = true;
			schema = autoDbModel.getDbAccount().toUpperCase();
		}
		resultInfo = new ResultInfo();
	}

	/**
	 * 判断数据库是否可以链接
	 */
	public boolean ifCanConnection(){
		Connection conn = getConnection();
		try {
			conn = getConnection();
			if(conn==null)
				return false;
			return true;
		} finally {
			close(conn, null, null);
		}
	}
	
	/**
	 * 获取所有表
	 */
	public ResultInfo getAllTableList() {
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			if (conn == null) {
				return resultInfo;
			}
			DatabaseMetaData dbMetaData = conn.getMetaData();
			// table type. Typical types are "TABLE", "VIEW", "SYSTEM
			// TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS",
			// "SYNONYM".
			String[] types = { "TABLE" };
			rs = dbMetaData.getTables(null, schema, "%", types);
			List<AutoDbTableModel> list=new ArrayList<AutoDbTableModel>();
			AutoDbTableModel model=null;
			while (rs.next()) {
				model=new AutoDbTableModel();
				model.setTableName(rs.getString("TABLE_NAME").toUpperCase());
				String remarks = rs.getString("REMARKS");
				if(StringUtils.isEmpty(remarks)&&ifOracleDb){
					remarks=getOracleTableComments(model.getTableName());
				}
				list.add(model);
			}
			resultInfo.setResult(true);
			resultInfo.setObj(list);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(conn, null, rs);
		}
		return resultInfo;
	}
	
	/**
	 * 获取某个表
	 */
	public ResultInfo getTableInfo(String tableName){
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			if (conn == null) {
				return resultInfo;
			}
			DatabaseMetaData dbMetaData = conn.getMetaData();
			String[] types = { "TABLE" };
			rs = dbMetaData.getTables(null, schema, tableName, types);
			
			if (rs.next()) {
				resultInfo.setResult(true);
				String remarks = rs.getString("REMARKS");
				if(StringUtils.isEmpty(remarks)&&ifOracleDb){
					remarks=getOracleTableComments(tableName);
				}
				resultInfo.setObj(remarks);
			}else{
				resultInfo.setMsg("未找到");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			resultInfo.setMsg("获取表信息失败");
		} finally {
			close(conn, null, rs);
		}
		return resultInfo;
	}

	/**
	 * @Title: getKeyColumn
	 * @Description: 获取主键列表
	 */
	public ResultInfo getKeyColumn(String tableName) {
		List<String> list = new ArrayList<String>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			if (conn == null) {
				return resultInfo;
			}
			DatabaseMetaData dbMetaData = conn.getMetaData();
			rs = dbMetaData.getPrimaryKeys(catalog, schema, tableName);
			while (rs.next())
				list.add(rs.getString("COLUMN_NAME"));
			resultInfo.setResult(true);
			resultInfo.setObj(list);
		} catch (SQLException e) {
			e.printStackTrace();
			resultInfo.setMsg("获取主键失败");
		} finally {
			close(conn, null, rs);
		}
		return resultInfo;
	}

	/**
	 * @Title: getColumns
	 * @Description: 获取分类
	 */
	public ResultInfo getColumns(String tableName) {
		List<ColumnInfo> list = new ArrayList<ColumnInfo>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			if (conn == null) {
				return resultInfo;
			}
			DatabaseMetaData dbMetaData = conn.getMetaData();
			rs = dbMetaData.getColumns(catalog, schema, tableName, null);
			while (rs.next()) {
				list.add(getColumnInfo(rs));
			}
			resultInfo.setResult(true);
			resultInfo.setObj(list);
		} catch (SQLException e) {
			e.printStackTrace();
			resultInfo.setMsg("获取表字段失败");
		} finally {
			close(conn, null, rs);
		}
		return resultInfo;
	}

	/**
	 * @Title: 获取字段
	 */
	private ColumnInfo getColumnInfo(ResultSet rs) throws SQLException {
		// 打印测试
		// 打印测试和后面的赋值不能同时开启，要不会报流关闭的异常，这个问题未发现原因
		// printRs(rs);
		ColumnInfo columnInfo = new ColumnInfo();
		columnInfo.setSqlColumnName(rs.getString("COLUMN_NAME"));
		columnInfo.setSqlDataType(rs.getInt("DATA_TYPE"));
		columnInfo.setSqlNullAble(rs.getInt("NULLABLE"));
		columnInfo.setSqlColumnSize(rs.getInt("COLUMN_SIZE"));
		columnInfo.setSqlRemarks(rs.getString("REMARKS"));
		if ("".equals(columnInfo.getSqlRemarks()) && ifOracleDb) {
			columnInfo.setSqlRemarks(getOracleColumnComments(
					rs.getString("TABLE_NAME"), rs.getString("COLUMN_NAME")));
		}
		columnInfo.setSqlColumnDef(rs.getString("COLUMN_DEF"));
		columnInfo.setSqlColumnSize(rs.getInt("COLUMN_SIZE"));
		columnInfo.setSqlDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
		columnInfo.setSqlIsAutoIncrement(rs.getString("IS_AUTOINCREMENT"));
		return columnInfo;
	}

	/**
	 * @Title: 获取oracle的表描述
	 */
	private String getOracleTableComments(String table) {
		String sql = "SELECT comments FROM user_tab_comments WHERE table_name='"
				+ table + "'";
		return queryForOne(sql);
	}

	/**
	 * @Title: 获取oracle的字段描述
	 */
	public String getOracleColumnComments(String table, String column) {
		String sql = "SELECT comments FROM user_col_comments WHERE table_name='"
				+ table + "' AND column_name = '" + column + "'";
		return queryForOne(sql);
	}	
	
	/**
	 * 获取数据库连接
	 */	
	private Connection getConnection() {
		try {
			Class.forName(getDbDriver());
			Connection conn = DriverManager.getConnection(
					autoDbModel.getDbUrl(), autoDbModel.getDbAccount(),
					autoDbModel.getDbPwd());
			return conn;
		} catch (Exception ex) {
			resultInfo.setMsg("数据库连接未建立");
			return null;
		}
	}

	/**
	 * 关闭连接
	 */
	private void close(Connection conn, Statement st, ResultSet rs) {
		try {
			if (rs != null)
				rs.close();
			if (st != null)
				st.close();
			if (conn != null)
				conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 结果打印
	 */
	public void printRs(ResultSet rs) {
		try {
			ResultSetMetaData rsmd = rs.getMetaData();
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				System.out.println(rsmd.getColumnName(i) + ":"
						+ rs.getString(rsmd.getColumnName(i)));
			}
			System.out.println();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public String queryForOne(String sql) {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next())
				return rs.getString("comments");
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			close(conn, st, rs);
		}
		return null;
	}
	
	/**
	 * 获取数据库驱动，若没有找到对应的驱动的话，则返回空
	 * @return
	 */
	private String getDbDriver(){
		for (DbTypes dbTypes: DbTypes.values()) {
			if(dbTypes.name().equalsIgnoreCase(autoDbModel.getDbType())){
				return dbTypes.getDriver();
			}
		}
		return null;
	}
	
	public enum DbTypes{
		mysql {
			@Override
			public String getDriver() {
				return "com.mysql.jdbc.Driver";
			}
		} ,
		oracle {
			@Override
			public String getDriver() {
				return "oracle.jdbc.driver.OracleDriver";
			}
			
		};
		public abstract String getDriver();
	}
	
}
